﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using System.Collections;
using System.Data;

namespace ReportBusiness
{
    public class uMESReportOracleDAL
    {
        private string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
        uMESPagingDataDTO PagingDataDTO = new uMESPagingDataDTO();
        /// <summary>
        /// 分页查询方法
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="intCurrentPageNum">当前页数</param>
        /// <param name="intPageSize">每页条数</param>
        /// /// <param name="connString">数据库连接串</param>
        /// <returns></returns>
        public uMESPagingDataDTO GetPagingDataIns(string strSQL, int intCurrentPageNum, int intPageSize, string connString = "")
        {
            uMESPagingDataDTO dto = new uMESPagingDataDTO();
            if (connString == "")
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    using (OracleCommand cmd = new OracleCommand("p_page.Pagination", connection))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        //分页索引。
                        OracleParameter pIndex = new OracleParameter("Pindex", OracleType.Number);
                        pIndex.Direction = ParameterDirection.Input;
                        pIndex.Value = intCurrentPageNum;

                        //要查询的sql语句
                        OracleParameter pSql = new OracleParameter("Psql", OracleType.VarChar);
                        pSql.Direction = ParameterDirection.Input;
                        pSql.Value = strSQL;

                        //页面显示的数据条数
                        OracleParameter pSize = new OracleParameter("Psize", OracleType.Number);
                        pSize.Direction = ParameterDirection.Input;
                        pSize.Value = intPageSize;

                        //返回的分页数
                        OracleParameter pCount = new OracleParameter("Pcount", OracleType.Number);
                        pCount.Direction = ParameterDirection.Output;

                        //返回的记录数
                        OracleParameter pRCount = new OracleParameter("Prcount", OracleType.Number);
                        pRCount.Direction = ParameterDirection.Output;

                        //当前页数据记录集
                        OracleParameter pDataTabe = new OracleParameter("v_cur", OracleType.Cursor);
                        pDataTabe.Direction = ParameterDirection.Output;

                        //添加存储过程的参数
                        cmd.Parameters.Add(pIndex);
                        cmd.Parameters.Add(pSql);
                        cmd.Parameters.Add(pSize);
                        cmd.Parameters.Add(pCount);
                        cmd.Parameters.Add(pRCount);
                        cmd.Parameters.Add(pDataTabe);

                        OracleDataAdapter da = new OracleDataAdapter(cmd);

                        try
                        {
                            DataTable dt = new DataTable(); //保存记录集
                            da.Fill(dt);// '//填充数据数据集
                            dto.PageCount = pCount.Value.ToString();// ';//获取分页数
                            dto.RowCount = pRCount.Value.ToString(); //';//获取记录数
                            dto.DBTable = dt;
                            return dto;
                        }
                        catch (OracleException E)
                        {
                            connection.Close();
                            throw new Exception(E.Message);
                        }
                    }
                }
            }
            else
            {
                using (OracleConnection connection = new OracleConnection(ConfigurationManager.AppSettings[connString]))
                {
                    using (OracleCommand cmd = new OracleCommand("p_page.Pagination", connection))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        //分页索引。
                        OracleParameter pIndex = new OracleParameter("Pindex", OracleType.Number);
                        pIndex.Direction = ParameterDirection.Input;
                        pIndex.Value = intCurrentPageNum;

                        //要查询的sql语句
                        OracleParameter pSql = new OracleParameter("Psql", OracleType.VarChar);
                        pSql.Direction = ParameterDirection.Input;
                        pSql.Value = strSQL;

                        //页面显示的数据条数
                        OracleParameter pSize = new OracleParameter("Psize", OracleType.Number);
                        pSize.Direction = ParameterDirection.Input;
                        pSize.Value = intPageSize;

                        //返回的分页数
                        OracleParameter pCount = new OracleParameter("Pcount", OracleType.Number);
                        pCount.Direction = ParameterDirection.Output;

                        //返回的记录数
                        OracleParameter pRCount = new OracleParameter("Prcount", OracleType.Number);
                        pRCount.Direction = ParameterDirection.Output;

                        //当前页数据记录集
                        OracleParameter pDataTabe = new OracleParameter("v_cur", OracleType.Cursor);
                        pDataTabe.Direction = ParameterDirection.Output;

                        //添加存储过程的参数
                        cmd.Parameters.Add(pIndex);
                        cmd.Parameters.Add(pSql);
                        cmd.Parameters.Add(pSize);
                        cmd.Parameters.Add(pCount);
                        cmd.Parameters.Add(pRCount);
                        cmd.Parameters.Add(pDataTabe);

                        OracleDataAdapter da = new OracleDataAdapter(cmd);

                        try
                        {
                            DataTable dt = new DataTable(); //保存记录集
                            da.Fill(dt);// '//填充数据数据集
                            dto.PageCount = pCount.Value.ToString();// ';//获取分页数
                            dto.RowCount = pRCount.Value.ToString(); //';//获取记录数
                            dto.DBTable = dt;
                            return dto;
                        }
                        catch (OracleException E)
                        {
                            connection.Close();
                            throw new Exception(E.Message);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 不分页查询方法
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="connString">数据库连接串</param>
        /// <returns></returns>
        public DataTable GetDataTable(string strSQL, string connString = "")
        {
            if (connString == "")
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    using (OracleCommand cmd = new OracleCommand(strSQL, connection))
                    {
                        cmd.CommandType = CommandType.Text;
                        OracleDataAdapter da = new OracleDataAdapter(cmd);

                        try
                        {
                            DataTable dt = new DataTable(); //保存记录集
                            da.Fill(dt);//填充数据数据集
                            return dt;
                        }
                        catch (OracleException E)
                        {
                            connection.Close();
                            throw new Exception(E.Message);
                        }
                    }
                }
            }
            else
            {
                using (OracleConnection connection = new OracleConnection(ConfigurationManager.AppSettings[connString]))
                {
                    using (OracleCommand cmd = new OracleCommand(strSQL, connection))
                    {
                        cmd.CommandType = CommandType.Text;
                        OracleDataAdapter da = new OracleDataAdapter(cmd);

                        try
                        {
                            DataTable dt = new DataTable(); //保存记录集
                            da.Fill(dt);//填充数据数据集
                            return dt;
                        }
                        catch (OracleException E)
                        {
                            connection.Close();
                            throw new Exception(E.Message);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数(例如数据添加、删除、更新操作)
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="connString">数据库连接串</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString, string connString = "")
        {
            if (connString == "")
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            int rows = cmd.ExecuteNonQuery();
                            return rows;
                        }
                        catch (OracleException E)
                        {
                            connection.Close();
                            throw new Exception(E.Message);
                        }
                    }
                }
            }
            else
            {
                using (OracleConnection connection = new OracleConnection(ConfigurationManager.AppSettings[connString]))
                {
                    using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            int rows = cmd.ExecuteNonQuery();
                            return rows;
                        }
                        catch (OracleException E)
                        {
                            connection.Close();
                            throw new Exception(E.Message);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条sql语句，实现数据库事务
        /// </summary>
        /// <param name="SQLStringList">sql语句</param>
        /// <param name="connString">数据库连接串</param>
        public void ExecuteSqlTran(ArrayList SQLStringList, string connString = "")
        {
            if (connString == "")
            {
                using (OracleConnection conn = new OracleConnection(connectionString))
                {
                    conn.Open();
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = conn;
                    OracleTransaction tx = conn.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        foreach (var item in SQLStringList)
                        {
                            string strsql = item.ToString();
                            if (strsql != "")
                            {
                                cmd.CommandText = strsql;
                                cmd.ExecuteNonQuery();
                            }
                        }
                        // 提交事务
                        tx.Commit();
                    }
                    catch (Exception e)
                    {
                        tx.Rollback();
                        throw new Exception(e.Message);
                    }
                }
            }
            else
            {
                using (OracleConnection conn = new OracleConnection(ConfigurationManager.AppSettings[connString]))
                {
                    conn.Open();
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = conn;
                    OracleTransaction tx = conn.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        foreach (var item in SQLStringList)
                        {
                            string strsql = item.ToString();
                            if (strsql != "")
                            {
                                cmd.CommandText = strsql;
                                cmd.ExecuteNonQuery();
                            }
                        }
                        // 提交事务
                        tx.Commit();
                    }
                    catch (Exception e)
                    {
                        tx.Rollback();
                        throw new Exception(e.Message);
                    }
                }
            }
        }
    }
}
